'use strict'

const titbit = require('titbit')
const pg = require('pg')
const {httpcli} = require('gohttp')

const wxkey = {

    appid:'wx063997dfc24c13ce',
    secret:'f827dfff507d4820f6eb87b71c3cc564'

}
const cert_path ='/usr/local/cert'

const app = new titbit({

    debug:true,
    https:true,
    cert:`${cert_path}/api.yidan.work.pem`,
    key:`${cert_path}/api.yidan.work.key`

});

const psql = new pg.Pool({  

    host:'127.0.0.1',
    post:5432,
    user:'lyd',
    password:'01316127aA',
    database:'lyd',
    max:8

})

//首页获取帖子列表
app.get('/post',async c=>{

    //过滤人数已满和超时帖子
    let date = new Date();
    let Y = date.getFullYear() + '-';
    let M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1) + '-';
    let D = date.getDate()<10?'0'+date.getDate():date.getDate() + ' ';
    let h = date.getHours()<10?'0'+date.getHours():date.getHours() + ':';
    let m = date.getMinutes()<10?'0'+date.getMinutes():date.getMinutes() + ':';
    let s = date.getSeconds()<10?'0'+date.getSeconds():date.getSeconds(); 
    let date1 = Y+M+D+h+m+s
    
    //let sqlStr3 = 'select post.post_id,post.post_content,post.post_deadline,post.post_now_num,post.post_restrict_grade,post.post_restrict_major,post.post_restrict_sex,'
    //+'post.post_require_num,post.post_time,users.user_id,users.user_name,users.user_image_src from post,users where post_nameId=user_id'

    let sqlStr3 = 'select post.post_id,post.post_content,post.post_deadline,post.post_now_num,post.post_restrict_grade,post.post_restrict_major,post.post_restrict_sex,'
    +`post.post_require_num,post.post_time,users.user_id,users.user_name,users.user_image_src from post,users where post.post_nameId=users.user_id and post.post_now_num<post.post_require_num and post.post_deadline>'${date1}'`
    let r3 = await psql.query(sqlStr3)

    c.send(r3.rows)

})

//首页关键字搜索帖子
app.post('/search',async c =>{

    let data = JSON.parse(c.body)
    let key = data.key

    let sqlStr = 'select post.post_id,post.post_content,post.post_deadline,post.post_now_num,post.post_restrict_grade,post.post_restrict_major,post.post_restrict_sex,'
    +`post.post_require_num,post.post_time,users.user_id,users.user_name,users.user_image_src from post,users where post.post_content like '%${key}%' and post.post_nameId=users.user_id `
    let r = await psql.query(sqlStr)

    c.send(r.rows)
})

//用户登录获取openId
app.get('/login/:code',async c => {

    let auth_url = 'https://api.weixin.qq.com/sns/jscode2session'+
    `?appid=${wxkey.appid}`+
    `&secret=${wxkey.secret}`+
    `&js_code=${c.param.code}`+
    '&grant_type=authorization_code'

    let ret = await httpcli.get(auth_url)
                        .then(res =>{
                            return res.json()
                        })
    
    c.send(ret)

})

//存储新登录用户的信息到数据库
app.post('/newUser',async c =>{

    let data = JSON.parse(c.body)
    let userId = data.user_id
    let userName = data.user_name
    let userImage = data.user_image_src
    let date = new Date();
    let Y = date.getFullYear() + '-';
    let M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1) + '-';
    let D = date.getDate()<10?'0'+date.getDate():date.getDate() + ' ';
    let h = date.getHours()<10?'0'+date.getHours():date.getHours() + ':';
    let m = date.getMinutes()<10?'0'+date.getMinutes():date.getMinutes() + ':';
    let s = date.getSeconds()<10?'0'+date.getSeconds():date.getSeconds(); 
    let createDay = Y+M+D+h+m+s 

    let sqlStr = 'insert into users(user_id,user_name,user_image_src,user_phone,user_birth,user_sex,user_day,user_place) values ($1,$2,$3,$4,$5,$6,$7,$8)'
    let r = await psql.query(sqlStr,[userId,userName,userImage,'无','无',1,createDay,'无'])

    c.send('user created successfully')

})

//用户获取个人帖子列表
app.get('/myPost/:user_id',async c =>{

    let sqlStr = 'select * from post where post_nameId = $1'
    let r = await psql.query(sqlStr,[c.param.user_id])

    c.send(r.rows)

})

//用户删除个人帖子
app.delete('/myPost/:post_id',async c =>{

    let postId = c.param.post_id
    let sqlStr = 'delete  from post where post_id = $1'
    let r = await psql.query(sqlStr,[postId])
    let sqlStr1 = 'delete from apply where apply_post_id = $1'
    let r1 = await psql.query(sqlStr1,[postId])

    c.send('Deleted post successfully')

})

//实现发帖功能
app.post('/post',async c =>{

    let data = JSON.parse(c.body)
    let postContent = data.post_content
    let postTime = data.post_time
    let nameId = data.post_nameId
    let restrictSex = data.post_restrict_sex
    let restrictGrade = data.post_restrict_grade
    let restrictMajor = data.post_restrict_major
    let deadline = data.post_deadline
    let requireNum = data.post_require_num
    let nowNum = data.post_now_num
    let topic = data.post_topic
    let numId = data.post_num_id
    let restrictRemark = data.post_restrict_remark
    let restrictUniversity = data.post_restrict_university
    let shareNum = data.post_share_num
    let reportNum = data.post_report_num
    let ifrevoke = data.post_ifrevoke
    let ifdelete = data.post_ifdelete

    let sqlStr = 'insert into post(post_content,post_time,post_nameId,post_restrict_sex,post_restrict_grade,post_restrict_major,post_deadline,'
    +'post_require_num,post_now_num,post_topic,post_num_id,post_restrict_remark,post_restrict_university,post_share_num,post_report_num,post_ifrevoke,post_ifdelete)'
    +'values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17);'
    let r = await psql.query(sqlStr,[postContent,postTime,nameId,restrictSex,restrictGrade,restrictMajor,deadline,
    requireNum,nowNum,topic,numId,restrictRemark,restrictUniversity,shareNum,reportNum,ifrevoke,ifdelete])

    c.send('post successfully')

})

//用户查看收到的申请
app.get('/myReceive/:user_id',async c =>{

    //只显示待审核申请
    let userId = c.param.user_id
    let sqlStr = 'select apply.apply_post_id,apply.apply_id,post.post_content,users.user_id,users.user_name,users.user_image_src '
    +'from apply,users,post where post.post_nameId = $1 and apply.apply_post_id = post.post_id and users.user_id = apply.apply_user_id and apply.apply_state = 0'
    let r = await psql.query(sqlStr,[userId])

    c.send(r.rows)

})
//用户查看发出的申请
app.get('/myApply/:user_id',async c =>{

    let userId = c.param.user_id
    let sqlStr = 'select apply.apply_post_id,apply.apply_id,apply.apply_state,post.post_content,users.user_name,users.user_image_src from apply,post,users where apply_user_id = $1 and apply.apply_post_id = post.post_id and post.post_nameId = users.user_id'
    //let sqlStr = 'select users.user_name,users.user_image_src,apply.apply_post_id,apply.apply_state,post.post_content'
    //+'from users,apply,post where apply.apply_user_id = $1 and apply.apply_post_id = post.post_id and post.post_nameId = users.user_id'
    let r = await psql.query(sqlStr,[userId])

    c.send(r.rows)
    
})
//用户发出申请
app.post('/apply',async c =>{
    
    let data = JSON.parse(c.body)
    let date = new Date();
    let Y = date.getFullYear() + '-';
    let M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1) + '-';
    let D = date.getDate()<10?'0'+date.getDate():date.getDate() + ' ';
    let h = date.getHours()<10?'0'+date.getHours():date.getHours() + ':';
    let m = date.getMinutes()<10?'0'+date.getMinutes():date.getMinutes() + ':';
    let s = date.getSeconds()<10?'0'+date.getSeconds():date.getSeconds(); 
    let applyTime = Y+M+D+h+m+s
    let userId = data.user_id
    let postId = data.post_id

    let sqlStr = 'insert into apply(apply_user_id,apply_post_id,apply_state,apply_time) values($1,$2,$3,$4)'
    let r = await psql.query(sqlStr,[userId,postId,0,applyTime])

    c.send('applyed successfully')

})
//用户审核申请

//同意申请

app.post('/agree',async c =>{
    
    let data = JSON.parse(c.body)
    let applyId = data.apply_id
    let postId = data.apply_post_id

    let sqlStr = `update apply set apply_state = 1 where apply_id = ${applyId}`
    let r = await psql.query(sqlStr)

    //组队人数加一
    let sqlStr1 = `update post set post_now_num=post_now_num+1 where post_id = ${postId}`
    let r1 = await psql.query(sqlStr1)

    c.send('team successfully')

})

//拒绝申请

app.post('/refuse',async c =>{
    
    let data = JSON.parse(c.body)
    let applyId = data.apply_id

    let sqlStr = `update apply set apply_state = 2 where apply_id = ${applyId}`
    let r = await psql.query(sqlStr)

    c.send('refused successfully')

})

/*app.post('/applyState',async c =>{
    
    let data = JSON.parse(c.body)
    let applyId = data.apply_id
    let applyState = data.apply_state

    if(applyState == 1){
        let sqlStr = 'update apply set apply_state = 1 where apply_id = $1'
        let r = await psql.query(sqlStr,[applyId])
        //let sqlStr1 = 'update post set post_now_num=post_now_num+1 where post_id = (select apply_post_id from apply where apply_id = $2)'
        //let r1 = await psql.query(sqlStr1,[applyId])
    }

    else{

        let sqlStr = 'update apply set apply_state = 2 where apply_id = $2'
        let r = await psql.query(sqlStr,[applyId])

    } 
    
    c.send('changed state successfully')

})*/
//用户删除申请
app.delete('/myApply/:apply_id',async c =>{

    let applyId = c.param.apply_id
    let sqlStr = 'delete from apply where apply_id = $1'
    let r = psql.query(sqlStr,[applyId])

    c.send('deleted apply successfully')
    
})
//用户获取黑名单列表
app.get('/black/:user_id',async c =>{

    //let sqlStr = 'select black.black_id,black.black_post_id,post.post_content,users.user_id,users.user_name,users.user_image_src'
    //+'from post,users,black where black.black_post_id = post.post_id and post.post_nameId = users.user_id and black.black_nameid = $1'
    let sqlStr = 'select post.post_content,black.black_id,black.black_post_id,users.user_name,users.user_image_src from black,post,users where black.black_nameid = $1 and black.black_post_id = post.post_id and post.post_nameId = users.user_id'
    let r = await psql.query(sqlStr,[c.param.user_id])

    c.send(r.rows)

})

//用户举报指定帖子并加入黑名单
app.post('/black',async c =>{

    let data = JSON.parse(c.body)
    let date = new Date();
    let Y = date.getFullYear() + '-';
    let M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1) + '-';
    let D = date.getDate() + ' ';
    let h = date.getHours() + ':';
    let m = date.getMinutes() + ':';
    let s = date.getSeconds(); 
    let joinTime = Y+M+D+h+m+s 

    let sqlStr = 'insert into black(black_nameid,black_post_id,black_join_time) values($1,$2,$3)'
    let r = await psql.query(sqlStr,[data.black_nameid,data.black_post_id,joinTime])

    c.send('added black successfully')

})

//用户将指定帖子移出黑名单
app.post('/deleteBlack',async c =>{
    
    let data = JSON.parse(c.body)
    let blackNameId = data.user_id
    let blackPostId = data.black_post_id
    let sqlStr = 'delete from black where black_nameid = $1 and black_post_id = $2'
    let r = psql.query(sqlStr,[blackNameId,blackPostId])

    c.send('Removed from blacklist successfully')

})

//用户分享
app.post('/share',async c =>{

    let data = JSON.parse(c.body)
    let shareUserId = data.share_user_id
    let shareTitle = data.share_title
    let shareContent = data.share_content
    let shareType = data.share_type
    let shareImage = data.share_image_src
    let shareTime = data.share_time
    let shareLike = data.share_like

    let sqlStr='insert into share(share_user_id,share_title,share_content,share_type,share_image_src,share_time,share_like) values($1,$2,$3,$4,$5,$6,$7)'
    let r = await psql.query(sqlStr,[shareUserId,shareTitle,shareContent,shareType,shareImage,shareTime,shareLike])

    c.send('shared successfully')

})

//用户查看分享页面
app.get('/share', async c =>{

    let sqlStr = 'select share.*,users.user_name,users.user_image_src from share,users where share.share_user_id = users.user_id'
    let r = await psql.query(sqlStr)

    c.send(r.rows)

})
//用户查看个人信息
app.get('/information/:user_id',async c=>{

    let sqlStr = 'select * from users where user_id = $1'
    let r = await psql.query(sqlStr,[c.param.user_id])

    c.send(r.rows)

})

//用户修改个人信息
app.post('/information',async c=>{
    
    let data = JSON.parse(c.body)
    let userName = data.user_name
    let userImage = data.user_image_src
    let userPhone = data.user_phone
    let userBirth = data.user_birth
    let userSex = data.user_sex
    let userPlace = data.user_place
    let userId = data.user_id

    let sqlStr = 'update users set user_name = $1,user_image_src = $2,user_phone = $3,user_birth = $4,user_sex = $5,user_place = $6 where user_id = $7'
    let r = psql.query(sqlStr,[userName,userImage,userPhone,userBirth,userSex,userPlace,userId])

    c.send('Information modified successfully')

})

//用户提交问题反馈
app.post('/problem',async c =>{

    let data = JSON.parse(c.body)
    let tag = data.problem_tag
    let content = data.problem_content
    let imageSrc = data.problem_image_src
    let userId = data.problem_user_id

    let sqlStr = 'insert into problem(problem_tag,problem_content,problem_image_src,problem_user_id) values($1,$2,$3,$4)'
    let r = psql.query(sqlStr,[tag,content,imageSrc,userId])

    c.send('problem submited successfully')

})

//用户查看个人反馈列表
app.get('/myProblem/:user_id',async c =>{

    let userId = c.param.user_id
    let sqlStr = 'select * from problem where problem_user_id = $1'
    let r = await psql.query(sqlStr,[userId])

    c.send(r.rows)

})

//后台管理系统登录
app.post('/adminLogin',async (c,next)=>{

    c.setHeader('Access-Control-Allow-Origin','*');
    c.setHeader('Access-control-Allow-Methods','GET,POST');
    c.body=JSON.parse(c.body);

    //判断用户名是否正确
    let adminName=c.body.admin_name;
    let sqlStr1 = `select * from admin where admin_name=${adminName}`;
    let r1 = await psql.query(sqlStr1);
    
    if(r1.length===0){
        c.res.body = '该用户名不存在';
    }
    else{

    //用户名正确后判断密码是否正确
        let password = c.body.admin_password;
        let sqlStr2 =`select * from Student where admin_name = ${adminName}`;
        let r2 = await psql.query(sqlStr2);
        let str = JSON.stringify(r2[0]);
        let password1 = JSON.parse(str).admin_password;
        name = JSON.parse(str).Sname;
        let data = {name:name,bool:true};
        if(password === password1){
            c.send('登录成功')
        }
        else{
            c.send('密码错误')
        }
}
});

//后台管理系统获取帖子数量
app.get('/postNum',async c =>{

    let sqlStr = 'select count(*) from post'
    let r = await psql.query(sqlStr)
    c.send(r.rows)

})

//后台管理系统获取用户数量
app.get('/userNum',async c =>{
    
    let sqlStr = 'select count(*) from users'
    let r = await psql.query(sqlStr)
    c.send(r.rows)

})
//后台管理系统获取用户列表
app.get('/users',async c =>{

    let sqlStr = 'select * from users'
    let r = await psql.query(sqlStr)
    c.send(r.rows)

})

//后台管理系统删除用户
app.delete('/user:/user_id',async c => {

    let sqlStr = 'delete from users where user_id = $1'
    let r = await psql.query(sqlStr,[c.param.user_id])

    c.send('deleted user successfully')

})

//后台管理系统删除帖子
app.delete('/post/:post_id',async c =>{

    let postId = c.param.post_id
    let sqlStr = 'delete from post where post_id = $1'
    let r = await psql.query(sqlStr,[postId])

    c.send('deleted post successfully')

})

//后台管理系统获取用户问题反馈列表
app.get('/problem',async c =>{

    let sqlStr = 'select * from problem'
    let r = await psql.query(sqlStr)

    c.send(r.rows)

})

app.run(2004)
